1. Technical Field
This disclosure generally relates to the database systems, and more specifically relates to tools for managing database systems.
2. Background Art
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Retrieval of information from a database is typically done using queries. A query usually specifies conditions that apply to one or more columns of the database, and may specify relatively complex logical operations on multiple columns. The database is searched for records that satisfy the query, and those records that satisfy the query are returned as the query result. A popular query language is Structured Query Language (SQL), which has gained widespread acceptance in the database industry.
Database optimizers have been used to determine the best-performing method to run each query. A method for running a query is commonly referred to as a query access plan. Once an optimizer selects an access plan for executing a query, the optimizer typically uses the access plan each time the query is executed. Note, however, that changes over time can affect the execution time for a query using the selected access plan. The time for executing a query using a selected access plan is referred to herein as “query performance.” For example, changes in the optimizer code itself when fixes are applied to the database system or when a new release is installed may affect query performance. Changes to the SQL or query statements themselves when a new version of an application or fixes to an application are deployed can also affect query performance. Changes to the resources that the optimizer has to work with may also affect query performance. For example, resources that may affect query performance include: indexes, materialized query tables created by the user, the number of rows in each table, the size of the memory pool the application is running in, etc. Any of these changes may cause the optimizer to choose a quite different, and sometimes worse, query access plan. As a result, changes to a database system may inadvertently affect query performance.
Database administrators often need to monitor database performance and determine whether database performance can be improved. Analysis tools have long been provided to allow a user to capture SQL statements and queries running in an application. Note that the term “statement” is used herein broadly to encompass any suitable database statement, including queries. These analysis tools are typically used by developers and database administrators when tuning an application prior to deployment and again after deployment to find problem queries. Ideally, a user uses these tools to capture a baseline when the application is performing well, so that later, if any variables change and negatively impact the performance of the application, the tools can be used again to capture the SQL statements. Since a before and after set of optimizer statistics are now available, it is possible to scan for the worst-performing statements in the new set of optimizer monitor information, and then look for them in the baseline results in an attempt to manually figure out what is different. Today, this process for analyzing the two sets of results is labor-intensive, time-consuming and error-prone.
One of the problems with prior art methods is that known analysis tools only allow a user to focus on one set of optimizer results at a time. Given the restrictions of these known analysis tools, users today typically search for the longest running statements in the latest monitor and then look to the baseline results. Many time, however, the problem may actually be with shorter running statements that happen to run thousands and thousands of times. This can cause the user to have to search for dozens or even hundreds of statements in both sets of optimizer monitor information before finding the set of slower running SQL statements or queries. Furthermore, some applications rely on temporary tables whose schema names are not constant and typically vary by the user. Since the names are different, it makes searching for the corresponding statements in the baseline monitor even harder. Without a way to more efficiently compare items in a database system, the computer database industry will continue to suffer from the inefficient and error-prone methods discussed above for manually identifying database problems.